package com.epam.task.web.entity;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * User: zhunusbekova.arailym
 */
public class DataAccessLayer {

    /**
     * Singleton instance
     */
    public static final Logger LOGGER = LoggerFactory.getLogger(DataAccessLayer.class);
    private static final DataAccessLayer instance = new DataAccessLayer();
    private static final boolean NO = false;
    private static final int SUCCESS = 0;
    private static final int FAILING = -1;

    private String JDBC_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    private String JDBC_URL = "jdbc:mysql://localhost/biosfera";

    public String sDriver = "";
    public String sUrl = null;
    public int iTimeout = 30;
    public Connection conn = null;
    public Statement statement = null;


    public DataAccessLayer()
    {}

    /** Singleton instance getter
     * This method returns the unique instance
     * @return the unique instance
     */
    public static final DataAccessLayer getInstance()
    {
        return instance;
    }

    private void init(String sDriverVar, String sUrlVar)
    {
        this.setDriver(sDriverVar);
        this.setUrl(sUrlVar);
        this.setConnection();
        this.setStatement();
    }

    private void setDriver(String sDriverVar)
    {
        this.sDriver = sDriverVar;
    }

    private void setUrl(String sUrlVar)
    {
        this.sUrl = sUrlVar;
    }

    private void setConnection()
    {
        try
        {
            Class.forName(this.sDriver);
            this.conn = DriverManager.getConnection(this.sUrl);
        }
        catch (ClassNotFoundException e)
        {
            this.conn = null;
            e.printStackTrace();
        }
        catch (SQLException e)
        {
            this.conn = null;
            e.printStackTrace();
        }
    }

    public Connection getConnection()
    {
        return this.conn;
    }

    private void setStatement()
    {
        if (this.conn == null)
        {
            this.setConnection();
        }
        try
        {
            this.statement = this.conn.createStatement();
            this.statement.setQueryTimeout(this.iTimeout); // set timeout to 30 sec.
        }
        catch (Exception e)
        {
            System.err.println(e);
        }
    }

    public Statement getStatement()
    {
        return this.statement;
    }

    private void executeStatement(String instruction) throws SQLException
    {
        try
        {
            this.statement.executeUpdate(instruction);
        }
        catch (SQLException e)
        {
            System.out.println(instruction);
            System.err.println(e);
        }
    }

    // processes an array of instructions e.g. a set of SQL command strings passed from a file
    //NB you should ensure you either handle empty lines in files by either removing them or parsing them out
    // since they will generate spurious SQLExceptions when they are encountered during the iteration....
    public void executeStatement(String[] instructionSet) throws SQLException
    {
        try
        {
            for (int i = 0; i < instructionSet.length; i++)
            {
                this.executeStatement(instructionSet[i]);
            }
        }
        catch (SQLException e)
        {
            System.err.println(e);
        }
    }

    private ResultSet executeQuery(String instruction) throws SQLException
    {
        ResultSet rs = null;
        try
        {
            rs = this.statement.executeQuery(instruction);
            return rs;
        }
        catch (SQLException e)
        {
            System.err.println(e);
            return rs; // will be null so you can handle it in the calling statement as a null
        }
    }

    private void closeConnection()
    {
        try
        {
            this.conn.close();
        }
        catch (Exception e)
        {
            System.out.println(e);
        }
    }

    public ArrayList<Customer> getAllUsers() {
        ArrayList<Customer> users = new ArrayList<Customer>();
        try {
            this.init(JDBC_DRIVER_CLASS, JDBC_URL);

            ResultSet result = this.executeQuery("SELECT biosfera.oc_user.user_id, biosfera.oc_user.username, biosfera.oc_user.password,\n" +
                    "biosfera.oc_user_group.name as \"group\" FROM biosfera.oc_user,biosfera.oc_user_group\n" +
                    "WHERE biosfera.oc_user.user_group_id = biosfera.oc_user_group.user_group_id");

            while (result.next()) {
                String username = result.getString("username");
                String password = result.getString("password");
                UserGroup group = UserGroup.valueOf(result.getString("group").toUpperCase());
                long id = result.getLong("Id");

                Customer user = new Customer(id, group, username, password);

                users.add(user);
            }

            this.closeConnection();
        } catch (SQLException e) {
            LOGGER.error("SQL exception", e);
        }

        return users;
    }

    public List<Order> getAllOrders() {
        List<Order> orders = new ArrayList<Order>();
        try {
            this.init(JDBC_DRIVER_CLASS, JDBC_URL);


            ResultSet result = this.executeQuery("select\n" +
                    "    biosfera.oc_order.order_id,\n" +
                    "    biosfera.oc_order_status.name as 'status', \n" +
                    "    biosfera.oc_order.date_added,\n" +
                    "    sum(biosfera.oc_order_product.quantity) 'as totalquantity',\n" +
                    "    sum(biosfera.oc_order_product.quantity*biosfera.oc_order_product.price) 'as totalsum', \n" +
                    "    biosfera.oc_user.username\n" +
                    "from\n" +
                    "biosfera.oc_order,\n" +
                    "biosfera.oc_order_status,\n" +
                    "biosfera.oc_order_product,\n" +
                    "biosfera.oc_user\n" +
                    "where\n" +
                    "  biosfera.oc_user.user_id = biosfera.oc_order.user_id and\n" +
                    "biosfera.oc_order.order_id = biosfera.oc_order_product.order_id and\n" +
                    "biosfera.oc_order.order_status_id = biosfera.oc_order_status.order_status_id");

            while (result.next()) {
                long id = result.getLong("order_id");
                String status = result.getString("status");
                String dateString = result.getString("date_added");
                DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                java.util.Date dateAdded = new java.util.Date();
                try {
                    dateAdded = dateFormat.parse(dateString);
                } catch (ParseException e) {
                    LOGGER.error("Parse exception");
                    e.printStackTrace();
                }
                int quantity = result.getInt("totalQuantity");
                int sum = result.getInt("totalSum");
                long userId = result.getLong("user_id");

                Customer customer = new Customer(userId, null, null, null);
                Order order = new Order(id, status, dateAdded, quantity, sum, customer);
                orders.add(order);
            }

            for (int i = 0; i < orders.size(); i++) {
                try {
                    Order order = orders.get(i);
                    Customer customer = order.customer;
                    ResultSet uRes = this.executeQuery("SELECT biosfera.oc_user.user_id, biosfera.oc_user.username, biosfera.oc_user.password,\n" +
                            "biosfera.oc_user_group.name as \"group\" FROM biosfera.oc_user,biosfera.oc_user_group\n" +
                                    "WHERE biosfera.oc_user.user_group_id = biosfera.oc_user_group.user_group_id AND biosfera.oc_user.user_id == '" + customer.id + "'");

                    order.customer.password = uRes.getString("password");
                    order.customer.group = UserGroup.valueOf(uRes.getString("group").toUpperCase());
                    order.customer.username = uRes.getString("name");
                } catch (SQLException e) {
                    LOGGER.error("SQL exception", e);
                }
            }

            this.closeConnection();
        } catch (SQLException e) {
            LOGGER.error("SQL exception", e);
        }

        return orders;

    }

    /**
     * Get at the moment within ISO8601 format.
     *
     * @return Date and time in ISO8601 format.
     */
    private String getSqliteDateString(java.util.Date date) {
        String DATE_FORMAT_ISO8601 = "yyyy-MM-dd'T'HH:mm:ss.SSS";
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_ISO8601);
        return sdf.format(date);
    }

    synchronized public void postOrder(String username) {
        if (username == null) throw
                new IllegalArgumentException("The username argument to the postOrder method may not be null.");

        try {
            this.init(JDBC_DRIVER_CLASS, JDBC_URL);

            ResultSet result = this.executeQuery("SELECT biosfera.oc_user.user_id, biosfera.oc_user.username, biosfera.oc_user.password,\n" +
                    "biosfera.oc_user_group.name as \"group\" FROM biosfera.oc_user,biosfera.oc_user_group\n" +
                    "WHERE biosfera.oc_user.user_group_id = biosfera.oc_user_group.user_group_id AND biosfera.oc_user.username == '" + username + "'");

            String password = result.getString("password");
            UserGroup group = UserGroup.valueOf(result.getString("group").toUpperCase());
            long id = result.getLong("user_id");

            Customer customer = new Customer(id, group, username, password);
            Order order = new Order(id, "new", new java.util.Date(), 0, 0, customer);

            this.addOrder(order);
            this.closeConnection();
        } catch (SQLException e) {
            LOGGER.error("SQL exception", e);
        }
    }

    private void addOrder(Order order) {
        try {
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // set the format to sql date time
            java.util.Date date = new java.util.Date();

            this.executeStatement("INSERT INTO biosfera.oc_order (user_id, u, Time) VALUES " +
                    "(" + "'" + order.customer.id + "', " +
                    "'" + order.status + "', " +
                    "'" + dateFormat.format(date) /*getSqliteDateString(msg.timestamp)*/ + "'" +
                    ")");

        } catch (Exception e) {
            LOGGER.error("Got an exception! ", e.getMessage());
        }

    }

    synchronized public int loginUser(String username, String password) {
        return this.updateUserStatus(username, password);
    }

    synchronized public int logoutUser(String username, String password) {
        return this.updateUserStatus(username, password);
    }

    private int updateUserStatus(String username, String password) {
        if (username == null) throw
                new IllegalArgumentException("The username argument to the updateUserStatus method may not be null.");
        if (password == null) throw
                new IllegalArgumentException("The password argument to the updateUserStatus method may not be null.");

        int loginRes = FAILING;

        try {
            this.init(JDBC_DRIVER_CLASS, JDBC_URL);

            ResultSet result = this.executeQuery("SELECT biosfera.oc_user.user_id, biosfera.oc_user.username, biosfera.oc_user.password,\n" +
                    "biosfera.oc_user_group.name as \"group\" FROM biosfera.oc_user,biosfera.oc_user_group\n" +
                    "WHERE biosfera.oc_user.user_group_id = biosfera.oc_user_group.user_group_id AND biosfera.oc_user.username = '" + username + "' AND biosfera.oc_user.password = '" + password + "'");

            boolean isUserFound = result.next();

            if (isUserFound == NO) {
                loginRes = FAILING;
            } else {
                long id = result.getLong("Id");

                Customer customer = new Customer(id, null, username, password);
                this.executeUpdateUserStatus(customer);

                loginRes = SUCCESS;
            }

            this.closeConnection();
        } catch (SQLException e) {
            System.out.println(e);
        }

        return loginRes;
    }

    private void addUser(Customer user) {
        try {
            // user.state?1:0 - Convert boolean to int type
            this.executeStatement("INSERT INTO User (Name, Password, State) VALUES " +
                    "(" + "'" + user.username + "', " +
                    "'" + user.password + "', " +
                    ")");

        } catch (Exception e) {
            LOGGER.error("Got an exception! ", e.getMessage());
        }

    }

    private void executeUpdateUserStatus(Customer user) {
        try {
            // user.state?1:0 - Convert boolean to int type
            this.executeStatement("UPDATE User SET State='" + "' WHERE Id='" + user.id + "'");
        } catch (Exception e) {
            LOGGER.error("Got an exception! ", e.getMessage());
        }
    }

    public List<Customer> getUsersByStore() {
        List<Customer> users = new ArrayList<Customer>();
        try {
            this.init(JDBC_DRIVER_CLASS, JDBC_URL);

            ResultSet result = this.executeQuery("SELECT biosfera.oc_user.user_id, biosfera.oc_user.username, biosfera.oc_user.password,\n" +
                    "biosfera.oc_user_group.name as \"group\" FROM biosfera.oc_user,biosfera.oc_user_group\n" +
                    "WHERE biosfera.oc_user.user_group_id = biosfera.oc_user_group.user_group_id AND group == '" + UserGroup.USER + "'");

            while (result.next()) {
                String username = result.getString("username");
                String password = result.getString("password");
                UserGroup group = UserGroup.valueOf(result.getString("group").toUpperCase());
                long id = result.getLong("user_id");

                Customer user = new Customer(id, group, username, password);

                users.add(user);
            }

            this.closeConnection();
        } catch (SQLException e) {
            System.out.println(e);
        }

        return users;
    }


}
